﻿/*----------------------------------------------------------------
           // Copyright (C) 2008 安华顺通(北京)科技有限公司
           // 版权所有。 
           //
           // 文件名：OleDBHelper.cs
           // 文件功能描述：
           //
           // 
           // 创建标识：丁余良 2008 2008-10-22 11:42:26   建立文件
           //
           // 修改标识  //update1  at date...
           // 修改描述：
           //
           // 修改标识：//update2  at date...
           // 修改描述：
//----------------------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace RuleCode.Common
{
    public class OleDbHelper
    {
        private static OleDbHelper _Instance;
        private static int serverIndex = CurrentUser.runAtServer;
        

        private string _OleDbConnectionString;
        private static OleDbConnection  _OleDbConnection;
        private static OleDbTransaction  _OleDbTransaction;
        private static OleDbCommand  _OleDbCommand;

        public OleDbHelper()
        {
            _OleDbConnection = new OleDbConnection(_OleDbConnectionString);       
        }


        public bool BeginTransaction()
        {
            try
            {
                _OleDbTransaction = _OleDbConnection.BeginTransaction();
            }
            catch (Exception e)
            {
            }
            finally
            {
                
            }
            return (_OleDbTransaction != null);
        }

        public bool RollBack()
        {
            try
            {
                _OleDbTransaction.Rollback();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public bool Commit()
        {
            try
            {
                _OleDbTransaction.Commit();
                return true;
            }
            catch (Exception e)
            {
                _OleDbTransaction.Rollback();
                return false ;
            }
        }

        public bool IsTransacting
        {
            get 
            {
                if (_OleDbTransaction == null)
                    return false;
                return _OleDbTransaction.Connection !=null;
            }
        }


        /// <summary>
        /// 对OleDbHelper自身的调用 静态方法
        /// </summary>
        public static OleDbHelper Instance
        {
            get
            {
                if (_Instance == null)
                    _Instance = new OleDbHelper();
                return _Instance;
            }
        }

        /// <summary>
        /// 利用动态的创建SQL命令 获取 数据库读取的对象 OleDbDataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public OleDbDataReader ExecuteReader(string cmdText)
        {
            

            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = _OleDbConnection;
            cmd.CommandText = cmdText;
            OleDbDataReader rdr;
            try
            {
                if (_OleDbConnection.State != ConnectionState.Open)
                    _OleDbConnection.Open();
                 rdr = cmd.ExecuteReader();
                 return rdr;
               
            }
            catch
            {
                _OleDbConnection.Close();
                return null;
            }
            finally
            {
                
                
            }
           
        }

        public OleDbDataReader ExecuteReader(string connectionText,string cmdText)
        {


            OleDbCommand cmd = new OleDbCommand();
            OleDbConnection  connection= new OleDbConnection(connectionText) ;
            cmd.Connection = connection;
            cmd.CommandText = cmdText;
            OleDbDataReader rdr;
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                rdr = cmd.ExecuteReader();
                return rdr;

            }
            catch
            {
                connection.Close();
                return null;
            }
            finally
            {
               // _OleDbConnection.Close();
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="myPara"></param>
        /// <returns></returns>

        public OleDbDataReader ExecuteReader(string cmdText, CommandType cmdType, params OleDbParameter[] myParas)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = _OleDbConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            OleDbDataReader rdr;
            if (myParas != null)
            {
                foreach (OleDbParameter pama in myParas)
                    if (pama != null)
                        cmd.Parameters.Add(pama);
                    else
                        break;
            }

            try
            {
                if (_OleDbConnection.State != ConnectionState.Open)
                    _OleDbConnection.Open();
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {

                _OleDbConnection.Close();
                return null;
            }
            finally 
            {
                    
            }
           
        }

        /// <summary>
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="myPara"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params OleDbParameter[] myParas)
        {

            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = _OleDbConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            object val;
            if (myParas != null)
            {
                foreach (OleDbParameter pama in myParas)
                    if (pama != null)
                        cmd.Parameters.Add(pama);
                    else
                        break;
            }


            try
            {
                if (_OleDbConnection.State != ConnectionState.Open)
                    _OleDbConnection.Open();
                val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

            }
            catch
            {
                _OleDbConnection.Close();
                return null;
            }
            finally
            {
                if(!IsTransacting )
                    _OleDbConnection.Close();
               
            }
            return val;
        }
        /// <summary>
        /// ExecuteNonQuery
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="myParas"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params OleDbParameter[] myParas)
        {

            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = _OleDbConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (myParas != null)
            {
                foreach (OleDbParameter pama in myParas)
                    if (pama != null)
                        cmd.Parameters.Add(pama);
                    else
                        break;
            }

            try
            {
                if (_OleDbConnection.State != ConnectionState.Open)
                    _OleDbConnection.Open();
                int val = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.ToString());

            }
            finally
            {
                _OleDbConnection.Close();

            }
            return -1;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = _OleDbConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            OleDbTransaction trans = null;
            
            //修改说明：加了BegingTransaction和Commit看能否解决添加字段不稳定的问题。

            try
            {
                if (_OleDbConnection.State != ConnectionState.Open)
                    _OleDbConnection.Open();
                trans = _OleDbConnection.BeginTransaction();
                cmd.Connection = _OleDbConnection;
                cmd.Transaction = trans;
                int val = cmd.ExecuteNonQuery();
                trans.Commit();

                return val;
            }
            catch (Exception e0)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                //MessageBox.Show(e0.ToString());
                

            }
            finally
            {
                _OleDbConnection.Close();

            }
            return -1;
        }
    }
}
